package hanelsoft.vn.dictionaylearn.model.db;

import hanelsoft.vn.dictionaylearn.model.entity.InfoHistory;
import hanelsoft.vn.dictionaylearn.model.entity.InfoVocabulary;
import hanelsoft.vn.dictionaylearn.model.entity.InfoVocabularyContent;
import hanelsoft.vn.dictionaylearn.model.entity.InfoVocabularyContentDelete;
import hanelsoft.vn.dictionaylearn.model.entity.InfoVocabularyDelete;
import hanelsoft.vn.dictionaylearn.model.entity.InfoVocabularyEdit;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class VocabularySQLiteHelper extends SQLiteOpenHelper{
	String tag = "[MySQLiteHelper]";
	InfoVocabularyDelete infoDelete;
	InfoVocabularyEdit infoEdit;
	public VocabularySQLiteHelper(Context context) {
		super(context, DBDefinition.DATABASE_NAME, null, DBDefinition.VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		String create_table = "CREATE TABLE "+DBDefinition.TABLE_NAME_VOCABULARY+"("+DBDefinition.VOCABULARY_ID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+DBDefinition.VOCABULARY_NAME+" TEXT, "+DBDefinition.VOCABULARY_NAME_TABLE_CONTENT+" TEXT)";
		db.execSQL(create_table);
		String create_table_history = "CREATE TABLE "+DBDefinition.TABLE_NAME_HISTORY+"("+DBDefinition.HISTORY_ID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+DBDefinition.HISTORY_WORD_ID+" TEXT, "+DBDefinition.HISTORY_WORD+" TEXT)";
		db.execSQL(create_table_history);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS "+DBDefinition.TABLE_NAME_VOCABULARY);
		onCreate(db);
		db.execSQL("DROP TABLE IF EXISTS "+DBDefinition.TABLE_NAME_HISTORY);
		onCreate(db);
	}
	//---------------- Create Table
	public void createTableVocabulary(String TABLE_NAME) {
		SQLiteDatabase mDB = getWritableDatabase();
		String create_table = "CREATE TABLE "+TABLE_NAME+"("+DBDefinition.VOCABULARY_CONTENT_ID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+DBDefinition.VOCABULARY_CONTENT_WORD_ID+" TEXT, "+DBDefinition.VOCABULARY_CONTENT_WORD+" TEXT, "+DBDefinition.VOCABULARY_CONTENT_CONTENT+" TEXT)";
		mDB.execSQL(create_table);
	}
	//---------------- Change name Table
	public void changeNameTableVocabulary(String TABLE_NAME, String NEW_TABLE_NAME) {
		if(TABLE_NAME.equals(NEW_TABLE_NAME) == false)
		{
			if(TABLE_NAME.equals("") == false)
			{
				if(NEW_TABLE_NAME.equals("") == false)
				{
					SQLiteDatabase mDB = getWritableDatabase();
					String edit_table = "ALTER TABLE "+TABLE_NAME+" RENAME TO "+NEW_TABLE_NAME;
					mDB.execSQL(edit_table);
				}
			}
		}
	}
	//----------------- Insert into database
	public void addTableVocabulary(String name, String name_content)
	{
		SQLiteDatabase mDB = getWritableDatabase();
		ContentValues mValues = new ContentValues();
		mValues.put(DBDefinition.VOCABULARY_NAME, name);
		mValues.put(DBDefinition.VOCABULARY_NAME_TABLE_CONTENT, name_content);
		mDB.insert(DBDefinition.TABLE_NAME_VOCABULARY, null, mValues);
		mDB.close();
	}
	//----------------- Insert into database
	public void addTableVocabularyContent(String TABLE_NAME_CONTENT_VOCABULARY, String ID_WORD, String WORD, String CONTENT)
	{
		if(TABLE_NAME_CONTENT_VOCABULARY.equals("")== false && ID_WORD.equals("")== false && WORD.equals("")== false)
		{
			SQLiteDatabase mDB = getWritableDatabase();
			ContentValues mValues = new ContentValues();
			mValues.put(DBDefinition.VOCABULARY_CONTENT_WORD_ID, ID_WORD);
			mValues.put(DBDefinition.VOCABULARY_CONTENT_WORD, WORD);
			mValues.put(DBDefinition.VOCABULARY_CONTENT_CONTENT, CONTENT);
			mDB.insert(TABLE_NAME_CONTENT_VOCABULARY, null, mValues);
			mDB.close();
		}
	}
	//----------------- Insert into database
	public void addHistory(String ID_WORD, String WORD)
	{
		if(ID_WORD.equals("")== false && WORD.equals("")== false)
		{
			SQLiteDatabase mDB = getWritableDatabase();
			ContentValues mValues = new ContentValues();
			mValues.put(DBDefinition.HISTORY_WORD_ID, ID_WORD);
			mValues.put(DBDefinition.HISTORY_WORD, WORD);
			mDB.insert(DBDefinition.TABLE_NAME_HISTORY, null, mValues);
			mDB.close();
		}
	}
		//----------------- Update database
		public void updateTableVocabulary(int id, String NAME, String NAME_TABLE_CONTENT)
		{
			if(NAME.equals("")== false && NAME_TABLE_CONTENT.equals("")== false)
			{
				SQLiteDatabase mDB = getWritableDatabase();
				ContentValues mValue = new ContentValues();
				mValue.put(DBDefinition.VOCABULARY_NAME, NAME);
				mValue.put(DBDefinition.VOCABULARY_NAME_TABLE_CONTENT, NAME_TABLE_CONTENT);
				mDB.update(DBDefinition.TABLE_NAME_VOCABULARY, mValue, DBDefinition.VOCABULARY_ID+" = "+id, null);
				mDB.close();
			}
		}
		//----------------- Update database
		public void updateTableVocabularyContent(String TABLE_NAME_CONTENT_VOCABULARY, int id, String ID_WORD, String WORD, String CONTENT)
		{
			if(TABLE_NAME_CONTENT_VOCABULARY.equals("")== false && ID_WORD.equals("")== false && WORD.equals("")== false)
			{
				SQLiteDatabase mDB = getWritableDatabase();
				ContentValues mValue = new ContentValues();
				mValue.put(DBDefinition.VOCABULARY_CONTENT_WORD_ID, ID_WORD);
				mValue.put(DBDefinition.VOCABULARY_CONTENT_WORD, WORD);
				mValue.put(DBDefinition.VOCABULARY_CONTENT_CONTENT, CONTENT);
				mDB.update(TABLE_NAME_CONTENT_VOCABULARY, mValue, DBDefinition.VOCABULARY_CONTENT_ID+" = "+id, null);
				mDB.close();
			}
		}
		//----------------- Update database
		public void updateHistory(int id, String ID_WORD, String WORD)
		{
			if(DBDefinition.TABLE_NAME_CONTENT_VOCABULARY.equals("")== false && ID_WORD.equals("")== false && WORD.equals("")== false)
			{
				SQLiteDatabase mDB = getWritableDatabase();
				ContentValues mValue = new ContentValues();
				mValue.put(DBDefinition.HISTORY_WORD_ID, ID_WORD);
				mValue.put(DBDefinition.HISTORY_WORD, WORD);
				mDB.update(DBDefinition.TABLE_NAME_HISTORY, mValue, DBDefinition.HISTORY_ID+" = "+id, null);
				mDB.close();
			}
		}
		//---------------- Delete database
		public void deleteTableVocabulary(String Table_Name) {
			SQLiteDatabase mDB = getWritableDatabase();
			mDB.execSQL("DROP TABLE IF EXISTS "+Table_Name);
			mDB.close();
		}
		//----------------- Delete database
		public void deleteTableVocabularyContent(String TABLE_NAME_CONTENT_VOCABULARY, int id)
		{
			SQLiteDatabase mDB = getWritableDatabase();
			mDB.delete(TABLE_NAME_CONTENT_VOCABULARY, "id = "+id, null);
			mDB.close();
		}
		//----------------- Delete database
		public void deleteHistory()
		{
			SQLiteDatabase mDB = getWritableDatabase();
			mDB.delete(DBDefinition.TABLE_NAME_HISTORY, null, null);
			mDB.close();
		}
		//----------------- Delete database
		public void deleteWordVocabulary(String table_name, int id)
		{
			SQLiteDatabase mDB = getWritableDatabase();
			mDB.delete(table_name, "id = "+id, null);
			mDB.close();
		}
		//----------------- View One Item Note
		public InfoVocabulary viewItemVocabulary(int id)
		{
			SQLiteDatabase mDB = getWritableDatabase();
			Cursor mCursor = mDB.query(DBDefinition.TABLE_NAME_VOCABULARY, new String[]{DBDefinition.VOCABULARY_ID, DBDefinition.VOCABULARY_NAME, DBDefinition.VOCABULARY_NAME_TABLE_CONTENT}, DBDefinition.VOCABULARY_ID+" = "+id, null, null, null, null);
			if(mCursor != null)
			{
				mCursor.moveToFirst();
			}
			InfoVocabulary info = new InfoVocabulary(mCursor.getInt(0), mCursor.getString(1), mCursor.getString(2));
			mCursor.close();
			mDB.close();
			return info;
		}
		//----------------- View One Item Note
		public InfoVocabularyContent viewItemVocabularyContent(String TABLE_NAME_CONTENT_VOCABULARY, int id)
		{
			SQLiteDatabase mDB = getWritableDatabase();
			Cursor mCursor = mDB.query(TABLE_NAME_CONTENT_VOCABULARY, new String[]{DBDefinition.VOCABULARY_CONTENT_ID, DBDefinition.VOCABULARY_CONTENT_WORD_ID, DBDefinition.VOCABULARY_CONTENT_WORD, DBDefinition.VOCABULARY_CONTENT_CONTENT}, DBDefinition.VOCABULARY_CONTENT_ID+" = "+id, null, null, null, null);
			if(mCursor != null)
			{
				mCursor.moveToFirst();
			}
			InfoVocabularyContent info = new InfoVocabularyContent(mCursor.getInt(0), mCursor.getString(1), mCursor.getString(2), mCursor.getString(3));
			mCursor.close();
			mDB.close();
			return info;
		}
		//----------------- View One Item Note
		public InfoHistory viewItemHistory(int id)
		{
			SQLiteDatabase mDB = getWritableDatabase();
			Cursor mCursor = mDB.query(DBDefinition.TABLE_NAME_HISTORY, new String[]{DBDefinition.HISTORY_ID, DBDefinition.HISTORY_WORD_ID, DBDefinition.HISTORY_WORD}, DBDefinition.HISTORY_ID+" = "+id, null, null, null, null);
			if(mCursor != null)
			{
				mCursor.moveToFirst();
			}
			InfoHistory info = new InfoHistory(mCursor.getInt(0), mCursor.getString(1), mCursor.getString(2));
			mCursor.close();
			mDB.close();
			return info;
		}
		//------------------- viewAllVocabulary
		public ArrayList<InfoVocabulary> viewAllVocabulary()
		{
			SQLiteDatabase mDB = getWritableDatabase();
			String sql = "SELECT * FROM "+DBDefinition.TABLE_NAME_VOCABULARY+" ORDER BY "+DBDefinition.VOCABULARY_ID;
			Cursor mCursor = mDB.rawQuery(sql, null);
			ArrayList<InfoVocabulary> array = new ArrayList<InfoVocabulary>();
			InfoVocabulary info;
			if(mCursor.moveToFirst())
			{
				do
				{
					info = new InfoVocabulary(mCursor.getInt(0), mCursor.getString(1), mCursor.getString(2));
					array.add(info);
				}while(mCursor.moveToNext());
			}
			mCursor.close();
			mDB.close();
			return array;
		}
		//------------------- viewAllVocabularyContent
		public ArrayList<InfoVocabularyContent> viewAllVocabularyContent(String TABLE_NAME_CONTENT_VOCABULARY)
		{
			Log.i(tag, "Start viewAllVocabularyContent");
			SQLiteDatabase mDB = getWritableDatabase();
			Log.i(tag, "Start viewAllVocabularyContent2");
			String sql = "SELECT * FROM "+TABLE_NAME_CONTENT_VOCABULARY+" ORDER BY "+DBDefinition.VOCABULARY_CONTENT_ID;
			Log.i(tag, "Start viewAllVocabularyContent3");
			Cursor mCursor = mDB.rawQuery(sql, null);
			Log.i(tag, "Start viewAllVocabularyContent4");
			ArrayList<InfoVocabularyContent> array = new ArrayList<InfoVocabularyContent>();
			Log.i(tag, "Start viewAllVocabularyContent5");
			InfoVocabularyContent info;
			if(mCursor.moveToFirst())
			{
				do
				{
					Log.i(tag, "Start viewAllVocabularyContent6");
					info = new InfoVocabularyContent(mCursor.getInt(0), mCursor.getString(1), mCursor.getString(2), mCursor.getString(3));
					Log.i(tag, "Start viewAllVocabularyContent7");
					array.add(info);
					Log.i(tag, "Start viewAllVocabularyContent8");
				}while(mCursor.moveToNext());
			}
			mCursor.close();
			mDB.close();
			Log.i(tag, "Start viewAllVocabularyContent9");
			return array;
		}
		//------------------- viewAllVocabularyContent
		public ArrayList<InfoHistory> viewAllHistory()
		{
			SQLiteDatabase mDB = getWritableDatabase();
			String sql = "SELECT * FROM "+DBDefinition.TABLE_NAME_HISTORY+" ORDER BY "+DBDefinition.HISTORY_ID;
			Cursor mCursor = mDB.rawQuery(sql, null);
			ArrayList<InfoHistory> array = new ArrayList<InfoHistory>();
			InfoHistory info;
			if(mCursor.moveToFirst())
			{
				do
				{
					info = new InfoHistory(mCursor.getInt(0), mCursor.getString(1), mCursor.getString(2));
					array.add(info);
				}while(mCursor.moveToNext());
			}
			mCursor.close();
			mDB.close();
			return array;
		}
		//------------------- View all Delete Vocabulary
		public ArrayList<InfoVocabularyDelete> viewAllDeleteVocabulary()
		{
			SQLiteDatabase mDB = getWritableDatabase();
			String sql = "SELECT * FROM "+DBDefinition.TABLE_NAME_VOCABULARY+" ORDER BY "+DBDefinition.VOCABULARY_ID;
			Cursor mCursor = mDB.rawQuery(sql, null);
			ArrayList<InfoVocabularyDelete> array_delete = new ArrayList<InfoVocabularyDelete>();
			if(mCursor.moveToFirst())
			{
				do
				{
					infoDelete = new InfoVocabularyDelete(mCursor.getInt(0), mCursor.getString(1), mCursor.getString(2),0);
					array_delete.add(infoDelete);
				}while(mCursor.moveToNext());
			}
			mCursor.close();
			mDB.close();
			return array_delete;
		}
		//------------------- View all Delete Vocabulary Content
		public ArrayList<InfoVocabularyContentDelete> viewAllDeleteVocabularyContent(String table_name)
		{
			SQLiteDatabase mDB = getWritableDatabase();
			String sql = "SELECT * FROM "+table_name+" ORDER BY id";
			Cursor mCursor = mDB.rawQuery(sql, null);
			ArrayList<InfoVocabularyContentDelete> array_delete = new ArrayList<InfoVocabularyContentDelete>();
			if(mCursor.moveToFirst())
			{
				do
				{
					InfoVocabularyContentDelete infoDelete = new InfoVocabularyContentDelete(mCursor.getInt(0), mCursor.getString(1), mCursor.getString(2),0);
					array_delete.add(infoDelete);
				}while(mCursor.moveToNext());
			}
			mCursor.close();
			mDB.close();
			return array_delete;
		}
		//------------------- View all Edit Vocabulary
		public ArrayList<InfoVocabularyEdit> viewAllEditVocabulary()
		{
			SQLiteDatabase mDB = getWritableDatabase();
			String sql = "SELECT * FROM "+DBDefinition.TABLE_NAME_VOCABULARY+" ORDER BY "+DBDefinition.VOCABULARY_ID;
			Cursor mCursor = mDB.rawQuery(sql, null);
			ArrayList<InfoVocabularyEdit> array_edit = new ArrayList<InfoVocabularyEdit>();
			if(mCursor.moveToFirst())
			{
				do
				{
					infoEdit = new InfoVocabularyEdit(mCursor.getInt(0), mCursor.getString(1), mCursor.getString(2),0);
					array_edit.add(infoEdit);
				}while(mCursor.moveToNext());
			}
			mCursor.close();
			mDB.close();
			return array_edit;
		}
}

